/**
  * exec SP_SAVE_ASSEMBLY_BIN_MAP_DETAILS 1066, 'Bin1', 1, NULL 
  */

ALTER PROC SP_SAVE_ASSEMBLY_BIN_MAP_DETAILS

@PROCESSSTEP_ID 	BIGINT,
@BIN_NUMBER		VARCHAR(50),
@IS_CHECKED		BIT,
@DOWNSIZE_BIN_NUMBER	VARCHAR(50)

AS
BEGIN
	DECLARE @BIN_PROCESSSTEP_ID BIGINT
	DECLARE @sort_process_step_id_cursor CURSOR
	DECLARE @parent_proudct_id INT

	SET @DOWNSIZE_BIN_NUMBER = 0 --THIS IS SETTING TO ZERO BECAUSE DOWNSIZE LIST BOX IS REMOVED FROM JSP SCREEN.

	--SELECT @BIN_PROCESSSTEP_ID = ProcessStep_ID FROM fn_get_process_flow_backward_by_id ( @PROCESSSTEP_ID ) WHERE Process_Type_Name = 'SORT'

	--Getting the Parent Proudct Id By Giving the Assembly Process Step Id.
	SELECT @parent_proudct_id = P_Parent_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID

	--Cursor holds the Sort Process Step Ids for the Product.
	SET @sort_process_step_id_cursor = CURSOR FOR SELECT PS.PROCESSSTEP_ID FROM PROCESS_STEP PS, PRODUCT P WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P_PARENT_PRODUCT_ID = @parent_proudct_id AND PS_TYPE_NAME = 'SORT'
	OPEN @sort_process_step_id_cursor
	FETCH NEXT FROM @sort_process_step_id_cursor INTO @BIN_PROCESSSTEP_ID
	WHILE @@FETCh_STATUS = 0
	BEGIN

		IF ( ( SELECT COUNT(*) FROM Assembly_Bin_Mapping WHERE Assm_ProcessStep_ID = @PROCESSSTEP_ID AND Bin_ProcessStep_ID = @BIN_PROCESSSTEP_ID AND Bin_Number = @BIN_NUMBER ) = 0 )
		BEGIN
			IF( @IS_CHECKED = 0 )
			BEGIN
				INSERT INTO Assembly_Bin_Mapping VALUES ( @PROCESSSTEP_ID, @BIN_PROCESSSTEP_ID, @BIN_NUMBER, CURRENT_TIMESTAMP, @DOWNSIZE_BIN_NUMBER )
			END
			ELSE
			BEGIN
				UPDATE Assembly_Bin_Mapping SET Bin_Number = '' WHERE Assm_ProcessStep_ID = -1
			END
		END
		ELSE
		BEGIN
			--UPDATE Assembly_Bin_Mapping SET Bin_Number = @BIN_NUMBER
			--DELETE FROM Assembly_Bin_Mapping WHERE Assm_ProcessStep_ID = @PROCESSSTEP_ID AND Bin_Number = @BIN_NUMBER
			IF( @IS_CHECKED = 1 )
			BEGIN
				DELETE FROM Assembly_Bin_Mapping WHERE Assm_ProcessStep_ID = @PROCESSSTEP_ID AND Bin_ProcessStep_ID = @BIN_PROCESSSTEP_ID AND Bin_Number = @BIN_NUMBER
			END
			ELSE
			BEGIN
				UPDATE Assembly_Bin_Mapping SET Downsize_Bin_Number = @DOWNSIZE_BIN_NUMBER WHERE Assm_ProcessStep_ID = @PROCESSSTEP_ID AND Bin_ProcessStep_ID = @BIN_PROCESSSTEP_ID AND Bin_Number = @BIN_NUMBER
			END
		END				

		FETCH NEXT FROM @sort_process_step_id_cursor INTO @BIN_PROCESSSTEP_ID
	END

	CLOSE @sort_process_step_id_cursor 
	DEALLOCATE @sort_process_step_id_cursor
END